﻿using Inaction.DbSchame;
using Inaction.Mapping;
using Inaction.Rule;
using System;
using System.Data;
using System.Linq;
using System.Text;

namespace Inaction.Database.Schema
{
    public class SqlServerSchemaProvider : SchemaProviderBase
    {
        internal override int MaxParaCount { get { return 2100; } }

        public override string ParaFix { get { return "@"; } }

        public SqlServerSchemaProvider(string connectionstring)
            : base(connectionstring, SqlType.SqlServer) { }

        #region ' Sql Code'
        /*
         * TABLENAME:表名
         * COMMENTS:注释
         */
        protected override string SqlTables
        {
            get
            {
                return @"SELECT D.NAME AS TABLENAME
	                    ,F.VALUE       AS COMMENTS
                    FROM SYSCOLUMNS A
                    INNER JOIN SYSOBJECTS D ON A.ID = D.ID
	                    AND D.XTYPE = 'U'
	                    AND D.NAME <> 'DTPROPERTIES'
                    LEFT JOIN SYS.EXTENDED_PROPERTIES F ON D.ID = F.MAJOR_ID
	                    AND F.MINOR_ID = 0
                    WHERE A.COLORDER = 1";
            }
        }
        /*
         * VIEWNAME:视图名
         * SQL:视图Sql
         */
        protected override string SqlViews
        {
            get
            {
                return @"SELECT SYS.VIEWS.NAME      AS VIEWNAME
	                    ,SYS.SQL_MODULES.DEFINITION AS SQL
                    FROM SYS.VIEWS
                    INNER JOIN SYS.SQL_MODULES ON SYS.VIEWS.OBJECT_ID = SYS.SQL_MODULES.OBJECT_ID";
            }
        }
        /*
         * TABLENAME:表名
         * COLUMNNAME:列名
         * DATATYPE:数据类型
         * LENGTH:长度
         * PRECISION:精度
         * SCALE:小数位数
         * NULLABLE:是否可为空
         * ORDINAL:序号
         * DEFAULTVALUE:默认值
         * COMMENTS:注释
         */
        protected override string SqlColumns
        {
            get
            {
                return @"
					  SELECT SYS.TABLES.NAME               AS TABLENAME
	                        ,SYS.COLUMNS.NAME              AS COLUMNNAME
	                        ,SYS.TYPES.NAME                AS DATATYPE
	                        ,SYS.COLUMNS.MAX_LENGTH        AS LENGTH
	                        ,SYS.COLUMNS.PRECISION         AS PRECISION
	                        ,SYS.COLUMNS.SCALE             AS SCALE
	                        ,SYS.COLUMNS.IS_NULLABLE       AS NULLABLE
	                        ,SYS.COLUMNS.COLUMN_ID         AS ORDINAL
	                        ,SYSCOMMENTS.TEXT              AS DEFAULTVALUE
	                        ,SYS.EXTENDED_PROPERTIES.VALUE AS COMMENTS
                        FROM SYS.COLUMNS
                        INNER JOIN SYS.TABLES ON SYS.COLUMNS.OBJECT_ID = SYS.TABLES.OBJECT_ID
                        INNER JOIN SYS.TYPES ON SYS.COLUMNS.SYSTEM_TYPE_ID = SYS.TYPES.SYSTEM_TYPE_ID AND SYS.TYPES.NAME<>'sysname'
                        LEFT JOIN SYS.EXTENDED_PROPERTIES ON SYS.EXTENDED_PROPERTIES.MAJOR_ID = SYS.COLUMNS.OBJECT_ID
	                        AND SYS.EXTENDED_PROPERTIES.MINOR_ID = SYS.COLUMNS.COLUMN_ID
                        LEFT JOIN DBO.SYSCOMMENTS ON SYS.COLUMNS.DEFAULT_OBJECT_ID = SYSCOMMENTS.ID

                        UNION
						--视图
                        SELECT SYS.VIEWS.NAME              AS TABLENAME
	                        ,SYS.COLUMNS.NAME              AS COLUMNNAME
	                        ,SYS.TYPES.NAME                AS DATATYPE
	                        ,SYS.COLUMNS.MAX_LENGTH        AS LENGTH
	                        ,SYS.COLUMNS.PRECISION         AS PRECISION
	                        ,SYS.COLUMNS.SCALE             AS SCALE
	                        ,SYS.COLUMNS.IS_NULLABLE       AS NULLABLE
	                        ,SYS.COLUMNS.COLUMN_ID         AS ORDINAL
	                        ,SYSCOMMENTS.TEXT              AS DEFAULTVALUE
	                        ,SYS.EXTENDED_PROPERTIES.VALUE AS COMMENTS
                        FROM SYS.COLUMNS
                        INNER JOIN SYS.VIEWS ON SYS.COLUMNS.OBJECT_ID = SYS.VIEWS.OBJECT_ID
                        INNER JOIN SYS.TYPES ON SYS.COLUMNS.SYSTEM_TYPE_ID = SYS.TYPES.SYSTEM_TYPE_ID AND SYS.TYPES.NAME<>'sysname'
                        LEFT JOIN SYS.EXTENDED_PROPERTIES ON SYS.EXTENDED_PROPERTIES.MAJOR_ID = SYS.COLUMNS.OBJECT_ID
	                        AND SYS.EXTENDED_PROPERTIES.MINOR_ID = SYS.COLUMNS.COLUMN_ID
                        LEFT JOIN DBO.SYSCOMMENTS ON SYS.COLUMNS.DEFAULT_OBJECT_ID = SYSCOMMENTS.ID 
                        ORDER BY TABLENAME,COLUMN_ID";
            }
        }
        /*
         * CONSTRAINTNAME:约束名
         * TABLENAME:表名
         * COLUMNNAME:列名
         * REFERENCETABLE:外键引用的表
         * CONSTRAINTTYPE:约束类型:U:惟一索引,P:主键,R:外键
         */
        protected override string SqlConstraints
        {
            get
            {
                return
                   @"SELECT INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME       AS TABLENAME
	                        ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME AS CONSTRAINTNAME
	                        ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME     AS COLUMNNAME
	                        ,t.TABLE_NAME                                        AS REFERENCETABLE
	                        ,CASE SYS.OBJECTS.TYPE
		                        WHEN 'PK'
			                        THEN 'P'
		                        WHEN 'UQ'
			                        THEN 'U'
		                        WHEN 'F'
			                        THEN 'R'
		                        END                                                 CONSTRAINTTYPE
                        FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                        INNER JOIN SYS.OBJECTS 
                                ON SYS.OBJECTS.NAME 
                                  = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
                        LEFT JOIN (
	                        SELECT INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME
		                        ,INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME
	                        FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
	                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                               ON INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME 
                                 = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
	                        ) t ON t.CONSTRAINT_NAME = SYS.OBJECTS.NAME
                   UNION
                   SELECT   
                    sys.all_objects.name AS TABLENAME, 
                    sys.indexes.name	 AS CONSTRAINTNAME, 
                    sys.all_columns.name AS COLUMNNAME, 
                    NULL				 AS REFERENCETABLE,
                    'U'                  AS CONSTRAINTTYPE
                    FROM      sys.indexes INNER JOIN
                    sys.index_columns ON sys.index_columns.index_id = sys.indexes.index_id AND 
                    sys.indexes.object_id = sys.index_columns.object_id AND  sys.indexes.is_unique=1 AND  sys.indexes.is_primary_key=0
				     INNER JOIN
                    sys.all_objects ON sys.indexes.object_id = sys.all_objects.object_id AND sys.indexes.type > 0 AND 
                    sys.all_objects.type = 'U' INNER JOIN
                    sys.all_columns ON sys.all_columns.object_id = sys.indexes.object_id AND 
                    sys.all_columns.column_id = sys.index_columns.column_id
";
            }
        }
        protected override string SqlViewColumns
        {
            get
            {
                return @"SELECT VIEW_NAME AS VIEWNAME,
                         TABLE_NAME       AS TABLENAME,
                         COLUMN_NAME      AS COLUMNNAME
                         FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE";
            }
        }
        #endregion

        /*
SELECT TOP (10000) Id
	,NAME
	,Code
	,ParentDepartmentId
	,CreateTime
	,UpdateTime
	,Updater
FROM Department
WHERE (
		CreateTime > (
			SELECT MAX(CreateTime) AS Expr1
			FROM (
				SELECT TOP (100000) CreateTime
				FROM Department
				ORDER BY CreateTime
				) AS T
			)
		)
ORDER BY CreateTime
         */
        protected internal override string GetPageSql(Type type, int pageSize, int pageIndex)
        {
            var classInfo = ClassInfo.LoadInfo(type);
            if (pageIndex == 0)
            {
                return @"
SELECT   TOP (" + pageSize + @") {0}
FROM      " + classInfo.TableName;
            }

            return @"
SELECT   TOP (" + pageSize + @") {0}
FROM      " + classInfo.TableName + @"
WHERE   (CreateTime >
                    (SELECT   MAX(CreateTime) AS Expr1
                     FROM      (SELECT   TOP (" + pageIndex * pageSize + @") CreateTime
                                      FROM      " + classInfo.TableName + @"
                                      {1}
                                      ORDER BY CreateTime) AS T))
{1}
ORDER BY CreateTime
";
        }

        internal override Func<string, string> NameFormater
        {
            get
            {
                return new Func<string, string>((x) => { return string.Format("[{0}]", x); });
            }
        }

        internal override string FormatSql(string sql)
        {
            sql = sql.Replace("dbo.", "");
            sql = sql.Replace("DBO.", "");
            sql = sql.Replace("[", "");
            sql = sql.Replace("]", "");
            return sql;
        }

        #region 生成脚本
        public override string CreateScript(View view)
        {
            string sql = view.Sql;
            sql += Environment.NewLine;
            sql += "GO" + Environment.NewLine;
            return sql;
        }
        public override string CreateScript(DbSchame.Table table)
        {
            string sql = "CREATE TABLE [dbo].[" + table.Name + "]" + Environment.NewLine;
            sql += "(" + Environment.NewLine;
            sql += string.Join("," + Environment.NewLine, table.Columns.Select(p => CreateColumnScript(p)));
            if (table.ForeignKeys.Count != 0)
                sql += "," + Environment.NewLine;
            sql += string.Join("," + Environment.NewLine, table.ForeignKeys.Select(p => CreateForeignKeyScript(p)));
            sql += Environment.NewLine;
            sql += ")" + Environment.NewLine;
            sql += "GO" + Environment.NewLine;
            sql += string.Join(";" + Environment.NewLine, table.Columns.Select(p => CreateDescriptionScript(p)));
            sql += ";" + Environment.NewLine;
            sql += CreateDescriptionScript(table) + Environment.NewLine;
            sql += "GO" + Environment.NewLine;
            if (table.UniqueKeys.Count != 0)
            {
                sql += string.Join(";" + Environment.NewLine, table.UniqueKeys.Select(p => CreateUniqueKeyScript(p)));
                sql += ";" + Environment.NewLine;
                sql += "GO" + Environment.NewLine;
            }
            sql += Environment.NewLine;
            return sql;
        }

        private string CreateColumnScript(Column c)
        {
            string sql = "\t" + c.FormatName + " " + c.DbType + " " + (c.Nullable ? "NULL" : "NOT NULL");
            if (c.IsPrimaryKey) sql += " PRIMARY KEY";
            return sql;
        }

        private string CreateForeignKeyScript(ForeignKey fk)
        {
            string sql = "\tCONSTRAINT [" + fk.Name + "] FOREIGN KEY ([" + fk.Column.Name + "])"
                + " REFERENCES  [" + fk.ReferenceTable.Name + "] ([" + fk.ReferenceTable.PrimaryKey.Name + "])"
                + " ON UPDATE NO ACTION ON DELETE NO ACTION";
            return sql;
        }

        private string CreateDescriptionScript(Column c)
        {
            string sql = @"EXEC sp_addextendedproperty @name=N'MS_Description',@value=N'" + c.DisplayName + "',@level0type=N'Schema',@level0name=N'dbo',@level1type=N'Table',@level1name=N'" + c.Table.Name + "',@level2type=N'Column',@level2name=N'" + c.Name + "'";
            return sql;
        }

        private string CreateDescriptionScript(Table t)
        {
            string sql = @"EXEC sp_addextendedproperty @name=N'MS_Description',@value=N'" + t.DisplayName + "',@level0type=N'Schema',@level0name=N'dbo',@level1type=N'Table',@level1name=N'" + t.Name + "'";
            return sql;
        }

        private string CreateUniqueKeyScript(UniqueKey uk)
        {
            string sql = @"CREATE UNIQUE NONCLUSTERED INDEX [" + uk.Name + @"] ON [dbo].[" + uk.Table.Name + "] (" + string.Join(",", uk.Columns.Select(p => p.FormatName)) + @")";
            return sql;
        }

        public override string CreateDataInsertSql(DbSchame.Table table)
        {
            var data = GetTableDatas(table);
            StringBuilder sql = new StringBuilder();
            sql.AppendLine("//----------" + table.Name + ":" + (data == null ? 0 : data.Rows.Count) + "---------//");
            if (data != null && data.Rows.Count > 0)
            {
                foreach (DataRow row in data.Rows)
                {
                    sql.AppendLine("INSERT INTO " + table.FormatName
                        + "(" + string.Join(",", data.Columns.Cast<DataColumn>().Select(p => NameFormater(p.ColumnName))) + ") " +
                      @"VALUES (" + string.Join(",", row.ItemArray.Select(p => p == DBNull.Value ? "NULL" : string.Format("N'{0}'", Inaction.Util.TypeConvert.ConvertToDbValue(p)))) + ");");
                }
                sql.AppendLine();
                return sql.ToString();
            }
            return string.Empty;
        }
        #endregion
 
    }
}